﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Wpf.Telemetry.Service.Model;
using System.Data.SQLite;
using System.Data;

namespace Wpf.Telemetry.Service.DataAccess.SQLite
{
    public class SQLiteTelemetryRecorder : ITelemetryRecorder
    {
        private string connectionString;
        private const string insertApplicationCommand = "Insert into ApplicationInformation (Version, CreatedDate, UserSessionId, WorkingSet) values (@version, @CreatedDate, @userSessionId, @workingSet);SELECT last_insert_rowid()";
        private const string versionParam = "@version";
        private const string createdDateParam = "@CreatedDate";
        private const string userSessionIdParam = "@userSessionId";
        private const string workingSetParam = "@workingSet";

        private const string insertSystemCommand =
@"Insert into SystemInformation (OperatingSystem, IsTabletPC, ScreenWidth, ScreenHeight, RenderCapabilityTier, IsSlowMachine, ProcessorCount, ProcessorArchitecture, CreatedDate, UserNameHash, MachineNameHash, DomainHash, CLRVersion, PhysicalMemory)
values
(@OperatingSystem, @IsTabletPC, @ScreenWidth, @ScreenHeight, @RenderCapabilityTier, @IsSlowMachine, @ProcessorCount, @ProcessorArchitecture, @CreatedDate, @UserNameHash, @MachineNameHash, @DomainHash, @CLRVersion, @PhysicalMemory);SELECT last_insert_rowid()";
        private const string operatingSystemParam = "@OperatingSystem";
        private const string isTabletPcParam ="@IsTabletPC"; 
        private const string screenWidthParam = "@ScreenWidth"; 
        private const string screenHeightParam = "@ScreenHeight"; 
        private const string renderCapabilityTierParam = "@RenderCapabilityTier";
        private const string isSlowMachineParam = "@IsSlowMachine";
        private const string processorCountParam = "@ProcessorCount";
        private const string processorArchitectureParam = "@ProcessorArchitecture";
        private const string userNameHashParam = "@UserNameHash"; 
        private const string machineNameHashParam = "@MachineNameHash";
        private const string domainHashParam = "@DomainHash";
        private const string clrVersionParam = "@CLRVersion";
        private const string physicalMemoryParam = "@PhysicalMemory";

        private const string insertFeatureUsageRecordCommand = "Insert into FeatureUsageRecord (SystemInfoId, ApplicationInfoId, CreatedDate) values (@SystemInfoId, @ApplicationInfoId, @CreatedDate);SELECT last_insert_rowid()";
        private const string systemInfoIdParam = "@SystemInfoId";
        private const string applicationInfoIdParam = "@applicationInfoId";

        private const string insertFeatureUsageInfoCommand = "Insert into FeatureUsageInformation (FeatureUsageRecordId, FeatureName, UsageCount) values (@FeatureUsageRecordId, @FeatureName, @UsageCount)";
        private const string featureUsageRecordIdParam = "@FeatureUsageRecordId";
        private const string featureNameParam = "@FeatureName";
        private const string usageCountParam = "@UsageCount";

        private const string insertExceptionCommand = 
@"Insert into ExceptionInformation (CallStack, ExceptionName, Message, EmailAddress, ErrorDescription, CreatedDate, SystemInfoId)
values
(@CallStack, @ExceptionName, @Message, @EmailAddress, @ErrorDescription, @CreatedDate, @SystemInfoId)";
        private const string callStackParam = "@CallStack";
        private const string exceptionNameParam = "@ExceptionName";
        private const string messageParam = "@Message";
        private const string emailAddressParam = "@EmailAddress";
        private const string errorDescriptionParam = "@ErrorDescription";

        public SQLiteTelemetryRecorder(string connectionString)
        {
            if (string.IsNullOrEmpty(connectionString))
            {
                throw new ArgumentException("connection string cannot be null or empty", "connectionString");
            }
            this.connectionString = connectionString;
        }

        public void SaveFeatureUsage(ApplicationInformation appInfo, SystemInformation systemInfo, List<FeatureUsage> featureUsage)
        {
            using (SQLiteConnection cn = CreateConnection())
            using (SQLiteTransaction trans = cn.BeginTransaction())
            {
                try
                {
                    long appInfoId = SaveApplicationInfo(appInfo, cn, trans);
                    long sysInfoId = SaveSystemInformation(systemInfo, cn, trans);
                    long featureUsageId = SaveFeatureUsageRecord(cn, trans, sysInfoId, appInfoId);

                    foreach (FeatureUsage usage in featureUsage)
                    {
                        SaveFeatureUsageInfo(cn, trans, featureUsageId, usage);
                    }
                    trans.Commit();
                }
                catch (Exception)
                {
                    trans.Rollback();
                    throw;
                }
            }
        }

        public void SaveException(SystemInformation systemInfo, ExceptionInformation exceptionInfo)
        {
            using (SQLiteConnection cn = CreateConnection())
            using (SQLiteTransaction trans = cn.BeginTransaction())
            {
                try
                {
                    long sysInfoId = SaveSystemInformation(systemInfo, cn, trans);
                    SaveExceptionInformation(exceptionInfo, sysInfoId, cn, trans);

                    trans.Commit();
                }
                catch (Exception)
                {
                    trans.Rollback();
                    throw;
                }
            }
        }

        private void SaveExceptionInformation(ExceptionInformation exceptionInfo, long sysInfoId, SQLiteConnection cn, SQLiteTransaction trans)
        {
            SQLiteCommand cmd = new SQLiteCommand(insertExceptionCommand, cn, trans);
            cmd.Parameters.Add(new SQLiteParameter(callStackParam, DbType.String, 2500) { Value = exceptionInfo.CallStack});
            cmd.Parameters.Add(new SQLiteParameter(exceptionNameParam, DbType.String, 100) { Value = exceptionInfo.ExceptionName});
            cmd.Parameters.Add(new SQLiteParameter(messageParam, DbType.String, 200) { Value = exceptionInfo.Message});
            cmd.Parameters.Add(new SQLiteParameter(emailAddressParam, DbType.String, 200) { Value = exceptionInfo.EmailAddress});
            cmd.Parameters.Add(new SQLiteParameter(errorDescriptionParam, DbType.String, 1000) { Value = exceptionInfo.ErrorDescriptionFromUser });
            cmd.Parameters.Add(new SQLiteParameter(createdDateParam, DbType.DateTime) { Value = DateTime.Now });
            cmd.Parameters.Add(new SQLiteParameter(systemInfoIdParam, DbType.Int64) { Value = sysInfoId });

            cmd.ExecuteNonQuery();
        }

        private long SaveApplicationInfo(ApplicationInformation appInfo, SQLiteConnection cn, SQLiteTransaction trans)
        {
            SQLiteCommand cmd = new SQLiteCommand(insertApplicationCommand, cn, trans);
            
            cmd.Parameters.Add(versionParam, DbType.String, 25);
            cmd.Parameters[versionParam].Value = appInfo.Version;
            
            cmd.Parameters.Add(createdDateParam, DbType.DateTime);
            cmd.Parameters[createdDateParam].Value = DateTime.Now;

            cmd.Parameters.Add(userSessionIdParam, DbType.String, 40);
            cmd.Parameters[userSessionIdParam].Value = appInfo.UserSession.ToString();

            cmd.Parameters.Add(workingSetParam, DbType.Int64);
            cmd.Parameters[workingSetParam].Value = appInfo.WorkingSet;

            return (long) cmd.ExecuteScalar();
        }





        private long SaveSystemInformation(SystemInformation sysInfo, SQLiteConnection cn, SQLiteTransaction trans)
        {
            SQLiteCommand cmd = new SQLiteCommand(insertSystemCommand, cn, trans);

            cmd.Parameters.Add(new SQLiteParameter(operatingSystemParam, DbType.String, 255) { Value = sysInfo.OperatingSystem });
            cmd.Parameters.Add(new SQLiteParameter(isTabletPcParam, DbType.Boolean) { Value = sysInfo.IsTabletPc });
            cmd.Parameters.Add(new SQLiteParameter(screenWidthParam, DbType.Int32) { Value = sysInfo.ScreenWidth });
            cmd.Parameters.Add(new SQLiteParameter(screenHeightParam, DbType.Int32) { Value = sysInfo.ScreenHeight });
            cmd.Parameters.Add(new SQLiteParameter(renderCapabilityTierParam, DbType.Int32) { Value = sysInfo.RenderCapabilityTier });
            cmd.Parameters.Add(new SQLiteParameter(isSlowMachineParam, DbType.Boolean) { Value = sysInfo.IsSlowMachine });
            cmd.Parameters.Add(new SQLiteParameter(processorCountParam, DbType.Int32) { Value = sysInfo.ProcessorCount });
            cmd.Parameters.Add(new SQLiteParameter(processorArchitectureParam, DbType.String, 20) { Value = sysInfo.ProcessorArchitecture });
            cmd.Parameters.Add(new SQLiteParameter(createdDateParam, DbType.DateTime) { Value = DateTime.Now });
            cmd.Parameters.Add(new SQLiteParameter(userNameHashParam, DbType.Int32) { Value = sysInfo.UserNameHash });
            cmd.Parameters.Add(new SQLiteParameter(machineNameHashParam, DbType.Int32) { Value = sysInfo.ComputerNameHash });
            cmd.Parameters.Add(new SQLiteParameter(domainHashParam, DbType.Int32) { Value = sysInfo.DomainNameHash});
            cmd.Parameters.Add(new SQLiteParameter(clrVersionParam, DbType.String, 40) { Value = sysInfo.CLRVersion });
            cmd.Parameters.Add(new SQLiteParameter(physicalMemoryParam, DbType.Int64) { Value = sysInfo.PhysicalMemory / 1024 });

            return (long)cmd.ExecuteScalar();
        }



        private long SaveFeatureUsageRecord(SQLiteConnection cn, SQLiteTransaction trans, long sysInfoId, long appInfoId)
        {
            SQLiteCommand cmd = new SQLiteCommand(insertFeatureUsageRecordCommand, cn, trans);

            cmd.Parameters.Add(new SQLiteParameter(systemInfoIdParam, DbType.Int64) { Value = sysInfoId});
            cmd.Parameters.Add(new SQLiteParameter(applicationInfoIdParam, DbType.Int64) { Value = appInfoId });
            cmd.Parameters.Add(new SQLiteParameter(createdDateParam, DbType.DateTime) { Value = DateTime.Now });
         
            return (long)cmd.ExecuteScalar();
        }

        private void SaveFeatureUsageInfo(SQLiteConnection cn, SQLiteTransaction trans, long featureUsageRecordId, FeatureUsage usage)
        {
            SQLiteCommand cmd = new SQLiteCommand(insertFeatureUsageInfoCommand, cn, trans);

            cmd.Parameters.Add(new SQLiteParameter(featureUsageRecordIdParam, DbType.Int64) { Value = featureUsageRecordId});
            cmd.Parameters.Add(new SQLiteParameter(featureNameParam, DbType.String, 255) { Value = usage.FeatureName });
            cmd.Parameters.Add(new SQLiteParameter(usageCountParam, DbType.Int32) { Value = usage.UsageCount });

            cmd.ExecuteNonQuery();
        }

        
        private SQLiteConnection CreateConnection()
        {
            SQLiteConnection cn = new SQLiteConnection(connectionString);
            cn.Open();
            return cn;
        }

    }
}
